package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库连接
 *
 */
public class App 
{
    // 数据库连接信息
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/game";
    private static final String JDBC_USER = "root";
    private static final String JDBC_PASSWORD = "root";
    public static void main( String[] args )
    {
        // 连接数据库
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            // 插入数据
            insertUser(connection, "Alice", "alice@example.com");
            insertUser(connection, "Bob", "bob@example.com");

            // 查询数据
            List<User> users = selectUsers(connection);
            System.out.println("查询到的用户:");
            for (User user : users) {
                System.out.println(user);
            }

            // 更新数据
            updateUser(connection, 1, "Alice Smith", "alice.smith@example.com");

            // 查询数据（更新后）
            users = selectUsers(connection);
            System.out.println("更新后的用户:");
            for (User user : users) {
                System.out.println(user);
            }

            // 删除数据
            deleteUser(connection, 2);

            // 查询数据（删除后）
            users = selectUsers(connection);
            System.out.println("删除后的用户:");
            for (User user : users) {
                System.out.println(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 插入用户
    public static void insertUser(Connection connection, String name, String email) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.executeUpdate();
        }
    }

    // 查询用户
    public static List<User> selectUsers(Connection connection) throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT id, name, email FROM users";
        try (PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                users.add(new User(id, name, email));
            }
        }
        return users;
    }

    // 更新用户
    public static void updateUser(Connection connection, int id, String name, String email) throws SQLException {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setInt(3, id);
            pstmt.executeUpdate();
        }
    }

    // 删除用户
    public static void deleteUser(Connection connection, int id) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }
    }

}

class User {
    private int id;
    private String name;
    private String email;

    public User(int id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}
